-- EXEC SP_LOAD_ALL_TRANSIT_LOTS_SEARCH_BY_PSTYPE 'Tape & Reel, FG', 0, 8, 'FG', 0, 0
 
ALTER PROCEDURE SP_LOAD_ALL_TRANSIT_LOTS_SEARCH_BY_PSTYPE
(
 @PROCESS_TYPE		VARCHAR(50),
 @PROCESS_STEP_ID	INT,
 @LOCATION_ID		INT,
 @NEXT_PROCESS		VARCHAR(100),
 @SHIP_TOVENDORID	INT	, --USEFUL WHNE VENDOR INTERFACE	
 @SEARCH_LOT_ID     INT		
 )
AS
BEGIN
	
	DECLARE @PART_NUMBER 	VARCHAR(50)
	DECLARE @PREVIOUS_LOC 	VARCHAR(50)
	DECLARE @SHIP_QTY 	INT
	DECLARE @PS_TYPE VARCHAR(50)
	DECLARE @LOT_ID		BIGINT
	DECLARE @PROCESSTEP_ID INT
	DECLARE @DB_PROCESSSTEPID     INT
	DECLARE @SHIPPEDLOT_ID INT
	DECLARE @WAFERS_TABLE	TABLE ( WAFER_ID INT, QTY_IN INT )
	DECLARE @SHIPPED_WAFER_IDS_TABLE TABLE ( WAFER_ID INT)
	DECLARE @QTY_AS_STRING	VARCHAR(200)
	DECLARE @WAFER_ID_STR	VARCHAR(100)
	DECLARE @SHIP_NEXT_PROCESS	VARCHAR(50)	
	DECLARE @GORSS_DIE_PER_WAFER INT
	DECLARE @GROSS_DIE INT
	DECLARE @SHIPPED_WAFER_IDS VARCHAR(100)
	DECLARE @WAFER_ID VARCHAR(100)
	
	CREATE TABLE #RESULTS ( PROCESSSTEP_ID INT, PART_NUMBER VARCHAR(50),LOT_NUMBER VARCHAR(50),FAB_LOT_NUMBER VARCHAR(50),PS_TYPE VARCHAR(50),DATE_CODE VARCHAR(50),
				PREVIOUS_LOC VARCHAR(50),NEXT_LOC VARCHAR(100), NEXT_PROCESS VARCHAR(50), SHIP_DATE DATETIME, SHIP_QTY INT, GDPW INT, GROSS_DIE INT, SHIPPEDLOT_ID INT,WAFER_ID VARCHAR(150), LOT_ID BIGINT, DB_PROCESSSTEP_ID INT, BIN_QTY_STR VARCHAR(150) )

	DECLARE @TRANSITCURSOR CURSOR
	
	DECLARE @QUERYSTRING	VARCHAR(4000)

	SET @QUERYSTRING = N'INSERT INTO #RESULTS SELECT PS.PROCESSSTEP_ID, PRO.PART_NUMBER,LT.LOT_NUMBER, LT.FAB_LOT_NUMBER,  PS.PS_TYPE, LT.LOT_DATE_CODE,
			 VEN.V_NAME , SL.SL_NEXTLOCATION, SL.SL_NEXT_PROCESS, SL.SHIP_DATE, SL.SHIP_QTY, NULL, NULL, SL.SHIPPEDLOT_ID ,SL.WAFER_ID,SL.LOT_ID, NULL, NULL 
			FROM LOT_TABLE LT, SHIPPED_LOT SL, PRODUCT PRO, PROCESS_STEP PS, LOCATION LOC, VENDOR VEN
			WHERE SL.LOT_ID = LT.LOT_ID AND LT.PROCESSSTEP_ID = PRO.PROCESSSTEP_ID AND PRO.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
			 AND SL.STATUS = ''TRANSIT'' AND 
			LT.LOCATION_ID = LOC.LOCATION_ID AND LOC.VENDOR_ID = VEN.VENDOR_ID AND SL.SHIP_STATUS <= 0 '
	
	IF @PROCESS_TYPE IS NOT NULL AND @PROCESS_TYPE <> '0'
 	BEGIN
 		
		SET @QUERYSTRING = @QUERYSTRING + '  AND (( PS_TYPE IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING ('''+ @PROCESS_TYPE +''') ) AND SL_NEXT_PROCESS IS NULL )'
		
		SET @QUERYSTRING = @QUERYSTRING + ' OR ( SL_NEXT_PROCESS IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING ('''+ @NEXT_PROCESS +''') ) ) ) '
	END
	  
 	IF ( @PROCESS_STEP_ID IS NOT NULL AND @PROCESS_STEP_ID <> '0'  )
 	BEGIN
		
		SET @QUERYSTRING = @QUERYSTRING +' AND ( PRO.PROCESSSTEP_ID = ' + CONVERT(VARCHAR,@PROCESS_STEP_ID)
		
		SET @QUERYSTRING = @QUERYSTRING +' OR PRO.PROCESSSTEP_ID = (SELECT DISTINCT PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID (' + CONVERT(VARCHAR,@PROCESS_STEP_ID) +', 1 ) WHERE PS_TYPE = ''' + @NEXT_PROCESS + ''' ) )'
	END
	IF ( @LOCATION_ID IS NOT NULL AND @LOCATION_ID <> '0'  )
	BEGIN
		SET @QUERYSTRING = @QUERYSTRING +' AND LOC.VENDOR_ID = (SELECT VENDOR_ID FROM LOCATION WHERE LOCATION_ID = ' + CONVERT(VARCHAR,@LOCATION_ID) +' )'
 	END
	IF ( @SHIP_TOVENDORID IS NOT NULL AND @SHIP_TOVENDORID <> '0'  )
	BEGIN
 		SET @QUERYSTRING = @QUERYSTRING +' AND ( SL.SL_NEXTLOCATION IS NULL OR SL.SL_NEXTLOCATION = ( SELECT DISTINCT L_VENDOR_NAME FROM LOCATION WHERE VENDOR_ID = ' + CONVERT(VARCHAR,@SHIP_TOVENDORID) +') )'
 	END
	IF ( @SEARCH_LOT_ID IS NOT NULL AND @SEARCH_LOT_ID <> '0'  )
	BEGIN
 		SET @QUERYSTRING = @QUERYSTRING +' AND LT.LOT_ID = ' + CONVERT(VARCHAR,@SEARCH_LOT_ID)
 	END
	--PRINT '@QUERYSTRING :- '+@QUERYSTRING

	EXEC (@QUERYSTRING)
	--SELECT *FROM #RESULTS
	DECLARE @BASE_PROCESS_NAME  VARCHAR(50)

	IF @NEXT_PROCESS = 'Die Bank'
	BEGIN
		SET @TRANSITCURSOR = CURSOR FOR SELECT PROCESSSTEP_ID, PS_TYPE, PREVIOUS_LOC, SHIP_QTY, SHIPPEDLOT_ID, LOT_ID, NEXT_PROCESS, WAFER_ID FROM #RESULTS
			
		OPEN @TRANSITCURSOR
		FETCH NEXT FROM @TRANSITCURSOR INTO @PROCESSTEP_ID, @PS_TYPE, @PREVIOUS_LOC , @SHIP_QTY, @SHIPPEDLOT_ID, @LOT_ID, @SHIP_NEXT_PROCESS, @WAFER_ID 

		WHILE @@FETCH_STATUS = 0
		BEGIN

			SELECT @BASE_PROCESS_NAME = BASE_PROCESS_TYPE FROM PROCESS_TYPES_TABLE WHERE PROCESS_TYPE = @PS_TYPE
			
			SELECT @GORSS_DIE_PER_WAFER = Parameter_VALUE FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = (SELECT DISTINCT PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID (@PROCESSTEP_ID,0) WHERE PS_TYPE = 'FAB' ) AND Parameter_Key = 'Gross Die/Wafer'

			IF @BASE_PROCESS_NAME = 'FAB' OR @BASE_PROCESS_NAME = 'Wafer Store'
			BEGIN
				SELECT DISTINCT @DB_PROCESSSTEPID = PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID (@PROCESSTEP_ID,1) WHERE PS_TYPE_NAME = 'Die Bank'	
				SET @GROSS_DIE	= @SHIP_QTY * @GORSS_DIE_PER_WAFER		
			END
			ELSE IF @BASE_PROCESS_NAME = 'SORT' 
			BEGIN
				  SELECT DISTINCT @DB_PROCESSSTEPID = PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID (@PROCESSTEP_ID,1) WHERE PS_TYPE_NAME = 'Die Bank' 	
				  
				  SET @SHIPPED_WAFER_IDS	= REPLACE (@WAFER_ID, ';', ',' )		

				  INSERT INTO @SHIPPED_WAFER_IDS_TABLE SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING( @SHIPPED_WAFER_IDS  )

				  INSERT INTO @WAFERS_TABLE (WAFER_ID, QTY_IN) SELECT WAFER_ID, BIN_1_BALANCE_QTY FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @LOT_ID AND BIN_1_BALANCE_QTY > 0 
					AND WAFER_ID IN (SELECT WAFER_ID  FROM @SHIPPED_WAFER_IDS_TABLE ) ORDER bY CAST(WAFER_ID AS INT)				

				  SELECT @WAFER_ID_STR = COALESCE( @WAFER_ID_STR + ';' + CONVERT( VARCHAR(2), WAFER_ID ), CONVERT( VARCHAR(2) , WAFER_ID ) )
					 FROM @WAFERS_TABLE ORDER BY WAFER_ID 

					SELECT @QTY_AS_STRING = COALESCE( @QTY_AS_STRING + ';' + CONVERT( VARCHAR(10), QTY_IN ), CONVERT( VARCHAR(10) , QTY_IN ) )
					 FROM @WAFERS_TABLE ORDER BY WAFER_ID
	
				  SELECT @GROSS_DIE = ISNULL(SHIP_GOOD_DIE_QTY,0) FROM SHIPPED_LOT WHERE SHIPPEDLOt_ID = @SHIPPEDLOT_ID
				  IF @GROSS_DIE <= 0
				  BEGIN
						SELECT @GROSS_DIE = ISNULL(SUM(BIN_1_BALANCE_QTY), 0) FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @LOT_ID AND BIN_1_BALANCE_QTY > 0
						IF @GROSS_DIE <= 0
						BEGIN
							SET @GROSS_DIE = @SHIP_QTY * @GORSS_DIE_PER_WAFER
						END
				  END	
				
				  UPDATE #RESULTS SET WAFER_ID = ISNULL(@WAFER_ID_STR,@WAFER_ID) WHERE LOT_ID = @LOT_ID AND SHIPPEDLOT_ID = @SHIPPEDLOT_ID
			END
			ELSE IF @PS_TYPE = 'DIE BANK' AND @SHIP_NEXT_PROCESS <> 'Assembly'
			BEGIN	
					INSERT INTO @WAFERS_TABLE (WAFER_ID, QTY_IN) SELECT WAFER_ID, BIN_1_TRANSIT_QTY FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @LOT_ID AND BIN_1_TRANSIT_QTY > 0 ORDER bY CAST(WAFER_ID AS INT)

					SELECT @WAFER_ID_STR = COALESCE( @WAFER_ID_STR + ';' + CONVERT( VARCHAR(2), WAFER_ID ), CONVERT( VARCHAR(2) , WAFER_ID ) )
					 FROM @WAFERS_TABLE ORDER BY WAFER_ID 

					SELECT @QTY_AS_STRING = COALESCE( @QTY_AS_STRING + ';' + CONVERT( VARCHAR(10), QTY_IN ), CONVERT( VARCHAR(10) , QTY_IN ) )
					 FROM @WAFERS_TABLE ORDER BY WAFER_ID
					
					SET @DB_PROCESSSTEPID = @PROCESSTEP_ID
					
					SELECT @GROSS_DIE = ISNULL(SUM(BIN_1_TRANSIT_QTY), 0) FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @LOT_ID AND BIN_1_TRANSIT_QTY > 0
					UPDATE #RESULTS SET WAFER_ID = @WAFER_ID_STR WHERE LOT_ID = @LOT_ID AND SHIPPEDLOT_ID = @SHIPPEDLOT_ID
			END
			ELSE
			BEGIN
					UPDATE CLIENT_TABLE SET NAME = 'DUMMY' WHERE NAME = 'DUMMY'
					BREAK
			END
			
			UPDATE #RESULTS SET GDPW = @GORSS_DIE_PER_WAFER, GROSS_DIE = @GROSS_DIE, DB_PROCESSSTEP_ID = @DB_PROCESSSTEPID, BIN_QTY_STR = @QTY_AS_STRING WHERE LOT_ID = @LOT_ID AND SHIPPEDLOT_ID = @SHIPPEDLOT_ID
			
			DELETE FROM @WAFERS_TABLE

			SET @GORSS_DIE_PER_WAFER = NULL
			SET @GROSS_DIE = NULL			
			SET @DB_PROCESSSTEPID = NULL
			SET @QTY_AS_STRING	  = NULL
			SET @WAFER_ID_STR	  = NULL
			FETCH NEXT FROM @TRANSITCURSOR INTO @PROCESSTEP_ID, @PS_TYPE, @PREVIOUS_LOC, @SHIP_QTY, @SHIPPEDLOT_ID, @LOT_ID,@SHIP_NEXT_PROCESS, @WAFER_ID
			
		END
		CLOSE @TRANSITCURSOR
		DEALLOCATE @TRANSITCURSOR
	END

	SELECT PROCESSSTEP_ID , PART_NUMBER ,LOT_NUMBER ,FAB_LOT_NUMBER ,PS_TYPE ,DATE_CODE ,PREVIOUS_LOC ,NEXT_LOC,
			ISNULL(NEXT_PROCESS, @NEXT_PROCESS) AS NEXT_PROCESS, SHIP_DATE, SHIP_QTY, GDPW , GROSS_DIE, SHIPPEDLOT_ID,WAFER_ID, LOT_ID , DB_PROCESSSTEP_ID, BIN_QTY_STR
		FROM #RESULTS WHERE SHIP_QTY > 0 ORDER BY SHIP_DATE, PART_NUMBER DESC 
END

